<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.0">
  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">


<link rel="stylesheet" href="/lib/font-awesome/css/all.min.css">

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"yuzhenxin.gitee.io","root":"/","scheme":"Muse","version":"7.8.0","exturl":false,"sidebar":{"position":"right","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":true},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":false,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}}};
  </script>

  <meta name="description" content="数据库隔离级别数据库事务并发：  脏读：读取其他事务更新却没提交的事务（可回滚）自作聪明 不可重复读：前后读取字段值不同（中途被其他事务修改）黑客篡改 幻读：前后读取多了一些值（中途被其他事务修改）黑客增加数据  隔离级别：（从上到下级别升高）  读未提交READ UNCOMMITTED（都不能避免） 读已提交READ COMMITTED（避免脏读），Oracle默认级别，常用 可重复读REPEA">
<meta property="og:type" content="article">
<meta property="og:title" content="数据库">
<meta property="og:url" content="http://yuzhenxin.gitee.io/2020/11/20/%E6%95%B0%E6%8D%AE%E5%BA%93/index.html">
<meta property="og:site_name" content="余振鑫的Hexo博客">
<meta property="og:description" content="数据库隔离级别数据库事务并发：  脏读：读取其他事务更新却没提交的事务（可回滚）自作聪明 不可重复读：前后读取字段值不同（中途被其他事务修改）黑客篡改 幻读：前后读取多了一些值（中途被其他事务修改）黑客增加数据  隔离级别：（从上到下级别升高）  读未提交READ UNCOMMITTED（都不能避免） 读已提交READ COMMITTED（避免脏读），Oracle默认级别，常用 可重复读REPEA">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://gitee.com/yuzhenxin/images/raw/master/img/20210313154029.png">
<meta property="og:image" content="https://gitee.com/yuzhenxin/images/raw/master/img/20210313155746.png">
<meta property="og:image" content="https://gitee.com/yuzhenxin/images/raw/master/img/20210313203941.png">
<meta property="og:image" content="https://i.imgur.com/CFQj3Ov.png">
<meta property="og:image" content="https://i.imgur.com/ku158eo.png">
<meta property="article:published_time" content="2020-11-19T16:00:00.000Z">
<meta property="article:modified_time" content="2022-01-20T11:53:59.128Z">
<meta property="article:author" content="余振鑫">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://gitee.com/yuzhenxin/images/raw/master/img/20210313154029.png">

<link rel="canonical" href="http://yuzhenxin.gitee.io/2020/11/20/%E6%95%B0%E6%8D%AE%E5%BA%93/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>数据库 | 余振鑫的Hexo博客</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">余振鑫的Hexo博客</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
      <p class="site-subtitle" itemprop="description">计算机笔记</p>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a>

  </li>
  </ul>
</nav>




</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="http://yuzhenxin.gitee.io/2020/11/20/%E6%95%B0%E6%8D%AE%E5%BA%93/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="余振鑫">
      <meta itemprop="description" content="余振鑫的个人博客">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="余振鑫的Hexo博客">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          数据库
        </h1>

        <div class="post-meta">
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>

              <time title="创建时间：2020-11-20 00:00:00" itemprop="dateCreated datePublished" datetime="2020-11-20T00:00:00+08:00">2020-11-20</time>
            </span>
              <span class="post-meta-item">
                <span class="post-meta-item-icon">
                  <i class="far fa-calendar-check"></i>
                </span>
                <span class="post-meta-item-text">更新于</span>
                <time title="修改时间：2022-01-20 19:53:59" itemprop="dateModified" datetime="2022-01-20T19:53:59+08:00">2022-01-20</time>
              </span>

          

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <h1 id="数据库"><a href="#数据库" class="headerlink" title="数据库"></a>数据库</h1><h2 id="隔离级别"><a href="#隔离级别" class="headerlink" title="隔离级别"></a>隔离级别</h2><p>数据库事务并发：</p>
<ul>
<li>脏读：读取其他事务更新却没提交的事务（可回滚）<em>自作聪明</em></li>
<li>不可重复读：前后读取字段值不同（中途被其他事务修改）<em>黑客篡改</em></li>
<li>幻读：前后读取多了一些值（中途被其他事务修改）<em>黑客增加数据</em></li>
</ul>
<p>隔离级别：（从上到下级别升高）</p>
<ul>
<li>读未提交READ UNCOMMITTED（都不能避免）</li>
<li>读已提交READ COMMITTED（避免脏读），Oracle默认级别，常用</li>
<li>可重复读REPEATABLE READ（避免脏读和不可重复读）：防止其他事务对该字段更新，MYSQL默认级别</li>
<li>串行化SERIALIZABLE（全部避免）：防任何并发问题，性能很低</li>
</ul>
<h2 id="MySQL"><a href="#MySQL" class="headerlink" title="MySQL"></a>MySQL</h2><h3 id="工作常用到的语句"><a href="#工作常用到的语句" class="headerlink" title="工作常用到的语句"></a>工作常用到的语句</h3><p>between A and B（可以是数字，A 必须小于等于B，否则没有结果）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 修改主键自增</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> xx MODIFY id <span class="type">bigint</span> auto_increment;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 清空表内容（可恢复）</span></span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> xx;</span><br><span class="line"><span class="comment">-- 清空表内容（不可恢复），相当于删表重建</span></span><br><span class="line"><span class="keyword">TRUNCATE</span> <span class="keyword">TABLE</span> xx;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 日期格式化</span></span><br><span class="line">date_format(`create_time`,<span class="string">&#x27;%Y%m%d&#x27;</span>)</span><br><span class="line"><span class="comment">-- 毫秒转日期</span></span><br><span class="line">FROM_UNIXTIME(`<span class="type">timestamp</span>`<span class="operator">/</span><span class="number">1000</span>, <span class="string">&#x27;%Y%m%d&#x27;</span>)</span><br><span class="line"><span class="comment">-- 日期转毫秒</span></span><br><span class="line">UNIX_TIMESTAMP(`create_time`) </span><br></pre></td></tr></table></figure>

<h3 id="Dataworks语法"><a href="#Dataworks语法" class="headerlink" title="Dataworks语法"></a>Dataworks语法</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 日期格式化</span></span><br><span class="line">to_char(create_time, <span class="string">&#x27;yyyymmdd&#x27;</span>)</span><br><span class="line"><span class="comment">-- 毫秒转日期</span></span><br><span class="line">to_char(FROM_UNIXTIME(<span class="built_in">CAST</span>(SUBSTR(`<span class="type">timestamp</span>`,<span class="number">-13</span>,<span class="number">10</span>) <span class="keyword">AS</span> <span class="type">BIGINT</span>)),<span class="string">&#x27;yyyymmdd&#x27;</span>)</span><br><span class="line">get_json_object(temp_a.custom_properties,<span class="string">&#x27;$.session_start&#x27;</span>)</span><br></pre></td></tr></table></figure>

<h4 id="表的操作"><a href="#表的操作" class="headerlink" title="表的操作"></a>表的操作</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> student</span><br><span class="line">(</span><br><span class="line">        s_id <span class="type">int</span>(<span class="number">4</span>) <span class="keyword">not</span> <span class="keyword">null</span> <span class="keyword">PRIMARY</span> KEY auto_increment,</span><br><span class="line">        s_name <span class="type">varchar</span>(<span class="number">12</span>),</span><br><span class="line">        s_age <span class="type">int</span>(<span class="number">4</span>),</span><br><span class="line">        s_sex <span class="type">varchar</span>(<span class="number">2</span>),</span><br><span class="line">        s_date <span class="type">date</span>,</span><br><span class="line">        <span class="keyword">FOREIGN</span> KEY (Id_P) <span class="keyword">REFERENCES</span> Persons(Id_P)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> Persons <span class="keyword">values</span> (<span class="string">&#x27;Wilson&#x27;</span>, <span class="string">&#x27;Champs-Elysees&#x27;</span>);</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> Persons (LastName, Address) <span class="keyword">VALUES</span> (<span class="string">&#x27;Wilson&#x27;</span>, <span class="string">&#x27;Champs-Elysees&#x27;</span>);</span><br><span class="line"></span><br><span class="line">UPDATE Person <span class="keyword">SET</span> Address <span class="operator">=</span> <span class="string">&#x27;Zhongshan 23&#x27;</span>, City <span class="operator">=</span> <span class="string">&#x27;Nanjing&#x27;</span> <span class="keyword">WHERE</span> LastName <span class="operator">=</span> <span class="string">&#x27;Wilson&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">DELETE</span> <span class="keyword">FROM</span> Person <span class="keyword">WHERE</span> LastName <span class="operator">=</span> <span class="string">&#x27;Wilson&#x27;</span> ;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student s,score c;</span><br><span class="line"><span class="keyword">select</span> s2.s_name,<span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">as</span> num <span class="keyword">from</span> score s1 <span class="keyword">INNER</span> <span class="keyword">JOIN</span> student s2 <span class="keyword">on</span> s1.s_no <span class="operator">=</span> s2.s_id <span class="keyword">GROUP</span> <span class="keyword">BY</span> s2.s_name;<span class="comment">--分组查询</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> Company, OrderNumber <span class="keyword">FROM</span> Orders <span class="keyword">ORDER</span> <span class="keyword">BY</span> OrderNumber <span class="comment">--顺序显示，小-&gt;大</span></span><br><span class="line"><span class="keyword">SELECT</span> Company, OrderNumber <span class="keyword">FROM</span> Orders <span class="keyword">ORDER</span> <span class="keyword">BY</span> OrderNumber <span class="keyword">DESC</span> <span class="comment">--逆序显示，大-&gt;小</span></span><br><span class="line"><span class="comment">-- 聚集函数</span></span><br><span class="line"><span class="keyword">select</span> Sno,<span class="built_in">avg</span>(grade) <span class="keyword">as</span> 平均分 <span class="keyword">from</span> sc <span class="keyword">where</span> Sno<span class="operator">=</span>&quot;171303&quot;;</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="keyword">distinct</span> Sno) <span class="keyword">as</span> 选修学生总数 <span class="keyword">from</span> sc; </span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(Grade) <span class="keyword">as</span> 最高分,<span class="built_in">min</span>(Grade)<span class="keyword">as</span> 最低分 <span class="keyword">from</span> sc;</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">sum</span>(Grade) <span class="keyword">as</span> 总分 <span class="keyword">from</span> sc;</span><br><span class="line"><span class="comment">-- 分组查询</span></span><br><span class="line"><span class="keyword">select</span> Sdept,<span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">as</span> 人数 <span class="keyword">from</span> student <span class="keyword">group</span> <span class="keyword">by</span> Sdept;</span><br><span class="line"><span class="keyword">select</span> Sno,<span class="built_in">avg</span>(Grade) <span class="keyword">as</span> 均分 <span class="keyword">from</span> sc <span class="keyword">group</span> <span class="keyword">by</span> Sno <span class="keyword">having</span> <span class="built_in">avg</span>(Grade)<span class="operator">&gt;</span><span class="number">60</span>;</span><br><span class="line"><span class="comment">-- 等值与非等值查询</span></span><br><span class="line"><span class="keyword">select</span> student.<span class="operator">*</span>,sc.Grade,sc.Cno <span class="keyword">from</span> student,sc <span class="keyword">where</span> student.Sno<span class="operator">=</span>sc.Sno;</span><br></pre></td></tr></table></figure>

<table>
<thead>
<tr>
<th>通配符</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>%</td>
<td>替代一个或多个字符</td>
</tr>
<tr>
<td>_</td>
<td>仅替代一个字符</td>
</tr>
<tr>
<td>[charlist]</td>
<td>字符列中的任何单一字符</td>
</tr>
<tr>
<td>[^charlist]或者[!charlist]</td>
<td>不在字符列中的任何单一字符</td>
</tr>
</tbody></table>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- &quot;Persons&quot; 表中选取居住在以 &quot;N&quot; 开始的城市里的人</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> Persons <span class="keyword">WHERE</span> City <span class="keyword">LIKE</span> <span class="string">&#x27;N%&#x27;</span>;</span><br><span class="line"><span class="comment">-- 从 &quot;Persons&quot; 表中选取居住在不包含 &quot;lon&quot; 的城市里的人</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> Persons <span class="keyword">WHERE</span> City <span class="keyword">NOT</span> <span class="keyword">LIKE</span> <span class="string">&#x27;%lon%&#x27;</span>;</span><br><span class="line"><span class="comment">-- &quot;Persons&quot; 表中选取名字的第一个字符之后是 &quot;eorge&quot; 的人</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> Persons <span class="keyword">WHERE</span> FirstName <span class="keyword">LIKE</span> <span class="string">&#x27;_eorge&#x27;</span>;</span><br><span class="line"><span class="comment">--&quot;Persons&quot; 表中选取居住的城市以 &quot;A&quot; 或 &quot;L&quot; 或 &quot;N&quot; 开头的人</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> Persons <span class="keyword">WHERE</span> City <span class="keyword">LIKE</span> <span class="string">&#x27;[ALN]%&#x27;</span>;</span><br><span class="line"><span class="comment">--&quot;Persons&quot; 表中选取居住的城市不以 &quot;A&quot; 或 &quot;L&quot; 或 &quot;N&quot; 开头的人</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> Persons <span class="keyword">WHERE</span> City <span class="keyword">LIKE</span> <span class="string">&#x27;[!ALN]%&#x27;</span>;</span><br></pre></td></tr></table></figure>

<ul>
<li>JOIN: 如果表中有至少一个匹配，则返回行，INNER JOIN 与 JOIN 是相同的。</li>
<li>LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行</li>
<li>RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行</li>
<li>FULL JOIN: 只要其中一个表中存在匹配，就返回行</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student s,score c <span class="keyword">where</span> s.s_id <span class="operator">=</span> c.c_id;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student s <span class="keyword">INNER</span> <span class="keyword">JOIN</span> score c <span class="keyword">on</span> s.s_id <span class="operator">=</span> c.s_no; <span class="comment">-- 内连接</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student s <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> score c <span class="keyword">on</span> s.s_id <span class="operator">=</span> c.s_no; <span class="comment">-- 左连接</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student s <span class="keyword">RIGHT</span> <span class="keyword">JOIN</span> score c <span class="keyword">on</span> s.s_id <span class="operator">=</span> c.s_no; <span class="comment">-- 右连接</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student s <span class="keyword">FULL</span> <span class="keyword">JOIN</span> score c <span class="keyword">on</span> s.s_id <span class="operator">=</span> c.s_no;</span><br></pre></td></tr></table></figure>

<h3 id="join"><a href="#join" class="headerlink" title="join"></a>join</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">--内连接，取交集--</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">join</span> score <span class="keyword">on</span> user.id <span class="operator">=</span> score.id;</span><br><span class="line"><span class="comment">--左连接，左边一定要输出，右边匹配输出--</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">left</span> <span class="keyword">join</span> score <span class="keyword">on</span> user.id <span class="operator">=</span> score.id;</span><br><span class="line"><span class="comment">--右连接，右边一定要输出，左边匹配输出--</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">right</span> <span class="keyword">join</span> score <span class="keyword">on</span> user.id <span class="operator">=</span> score.id</span><br><span class="line"><span class="comment">--左右边都要输出，不匹配的为null--</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">left</span> <span class="keyword">join</span> score <span class="keyword">on</span> user.id <span class="operator">=</span> score.id</span><br><span class="line"><span class="keyword">union</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">right</span> <span class="keyword">join</span> score <span class="keyword">on</span> user.id <span class="operator">=</span> score.id;</span><br><span class="line"></span><br><span class="line"><span class="comment">--左边独有的--</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">left</span> <span class="keyword">join</span> score <span class="keyword">on</span> user.id <span class="operator">=</span> score.id <span class="keyword">where</span> score.id <span class="keyword">is</span> <span class="keyword">null</span>;</span><br><span class="line"><span class="comment">--右边独有的--</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">right</span> <span class="keyword">join</span> score <span class="keyword">on</span> user.id <span class="operator">=</span> score.id <span class="keyword">where</span> user.id <span class="keyword">is</span> <span class="keyword">null</span>;</span><br><span class="line"><span class="comment">--左边独有的+右边独有的--</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">left</span> <span class="keyword">join</span> score <span class="keyword">on</span> user.id <span class="operator">=</span> score.id <span class="keyword">where</span> score.id <span class="keyword">is</span> <span class="keyword">null</span></span><br><span class="line"><span class="keyword">union</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span> <span class="keyword">right</span> <span class="keyword">join</span> score <span class="keyword">on</span> user.id <span class="operator">=</span> score.id <span class="keyword">where</span> user.id <span class="keyword">is</span> <span class="keyword">null</span>;</span><br></pre></td></tr></table></figure>

<h4 id="on和where"><a href="#on和where" class="headerlink" title="on和where"></a>on和where</h4><p> on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。</p>
<p>where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。</p>
<h4 id="数据库的操作"><a href="#数据库的操作" class="headerlink" title="数据库的操作"></a>数据库的操作</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">DROP</span> DATABASE 数据库名称; <span class="comment">--删除数据库：</span></span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> 表名称; <span class="comment">--删除表（表的结构、属性以及索引也会被删除）</span></span><br><span class="line"><span class="keyword">TRUNCATE</span> <span class="keyword">TABLE</span> 表名称; <span class="comment">--除去表内的数据，但并不删除表本身</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> table_name <span class="keyword">ADD</span> column_name datatype;<span class="comment">--添加列</span></span><br><span class="line"><span class="keyword">ALTER</span> <span class="keyword">TABLE</span> table_name <span class="keyword">DROP</span> <span class="keyword">COLUMN</span> column_name;<span class="comment">--删除列</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">VIEW</span> view_name <span class="keyword">AS</span></span><br><span class="line"><span class="keyword">SELECT</span> column_name(s)</span><br><span class="line"><span class="keyword">FROM</span> table_name</span><br><span class="line"><span class="keyword">WHERE</span> <span class="keyword">condition</span></span><br></pre></td></tr></table></figure>

<h3 id="JDBC密码验证"><a href="#JDBC密码验证" class="headerlink" title="JDBC密码验证"></a>JDBC密码验证</h3><figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line"><span class="function"><span class="keyword">public</span> <span class="keyword">boolean</span> <span class="title">verifyPassword</span><span class="params">(String username, String password)</span> </span>&#123;</span><br><span class="line">    Connection con = getConnection(); <span class="comment">// 方法是个已有的方法可以获取到数据库连接</span></span><br><span class="line">    String sql = <span class="string">&quot;SELECT password FROM users WHERE user_name=?&quot;</span>;</span><br><span class="line">    PreparedStatement pst = <span class="keyword">null</span>;</span><br><span class="line">    ResultSet rs = <span class="keyword">null</span>;</span><br><span class="line">    <span class="keyword">boolean</span> flag = <span class="keyword">false</span>;</span><br><span class="line">    <span class="keyword">try</span> &#123;</span><br><span class="line">        pst = con.prepareStatement(sql);</span><br><span class="line">        pst.setObject(<span class="number">1</span>, username);</span><br><span class="line">        rs = pst.executeQuery();</span><br><span class="line">        <span class="keyword">while</span> (rs.next()) &#123;</span><br><span class="line">            <span class="keyword">if</span> (rs.getString(<span class="string">&quot;password&quot;</span>).equals(password)) &#123;</span><br><span class="line">                flag = <span class="keyword">true</span>;</span><br><span class="line">            &#125;</span><br><span class="line">        &#125;</span><br><span class="line">    &#125; <span class="keyword">catch</span> (ClassNotFoundException e) &#123;</span><br><span class="line">        e.printStackTrace();</span><br><span class="line">    &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">        e.printStackTrace();</span><br><span class="line">    &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            <span class="keyword">if</span> (rs != <span class="keyword">null</span>) rs.close();</span><br><span class="line">            <span class="keyword">if</span> (pst != <span class="keyword">null</span>) pst.close();</span><br><span class="line">            <span class="keyword">if</span> (con != <span class="keyword">null</span>) con.close();</span><br><span class="line">        &#125; <span class="keyword">catch</span> (SQLException e) &#123;</span><br><span class="line">            e.printStackTrace();</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">    <span class="keyword">return</span> flag;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h3 id="备份与恢复"><a href="#备份与恢复" class="headerlink" title="备份与恢复"></a>备份与恢复</h3><p>要备份的目录下：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqldump -uroot -p dbname &gt; dbname.sql</span><br></pre></td></tr></table></figure>

<p>恢复：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql -uroot -p dbname &lt; dbname.sql</span><br></pre></td></tr></table></figure>

<h3 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h3><p>Mysql建立索引（为了检索排序快的数据结构，增删改相应变慢）</p>
<h4 id="分类"><a href="#分类" class="headerlink" title="分类"></a>分类</h4><ul>
<li><p>主键索引（建表时自动创建）</p>
<p>innodb为<strong>聚簇索引</strong>，底层B+树（相较B树只在叶子结点存数据，且<strong>自动排序</strong>），根节点常驻内存</p>
<p><img src="https://gitee.com/yuzhenxin/images/raw/master/img/20210313154029.png" alt="image-20210313154029281"></p>
</li>
<li><p>单值索引/普通索引</p>
<p>一个索引只包含一列，可以有多个单值索引（如id index，name index）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 建表前添加索引，索引名默认列名</span></span><br><span class="line">creatr <span class="keyword">table</span> t_user(</span><br><span class="line">    id <span class="type">varchar</span>(<span class="number">20</span>) <span class="keyword">primary</span> key,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    key (name)</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 建表后添加索引</span></span><br><span class="line"><span class="keyword">CREATE</span> INDEX 索引名 <span class="keyword">ON</span> 表名 (字段);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 删除索引</span></span><br><span class="line"><span class="keyword">drop</span> index 索引名 <span class="keyword">on</span> 表名</span><br></pre></td></tr></table></figure></li>
<li><p>唯一索引</p>
<p>索引值唯一，但允许一个为null（对比主键索引不能为空）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 建表前添加索引，对比单值索引key</span></span><br><span class="line">creatr <span class="keyword">table</span> t_user(</span><br><span class="line">    id <span class="type">varchar</span>(<span class="number">20</span>) <span class="keyword">primary</span> key,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    <span class="keyword">unique</span> (name)</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 建表后添加索引</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">unique</span> INDEX 索引名 <span class="keyword">ON</span> 表名 (字段);</span><br></pre></td></tr></table></figure></li>
<li><p>复合索引</p>
<p>一个索引包含多个列，如（id，name）index，用在where id and name</p>
<p>原则：</p>
<ul>
<li>最左前缀原则，如abc，需为a、ab、abc</li>
<li>mysql会自动调整字段查询顺序以利用索引，如ba、acb、bac、cab、bca、cba</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 建表前添加索引，对比单值索引key</span></span><br><span class="line">creatr <span class="keyword">table</span> t_user(</span><br><span class="line">    id <span class="type">varchar</span>(<span class="number">20</span>) <span class="keyword">primary</span> key,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    age <span class="type">int</span>,</span><br><span class="line">    key (name,age)</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 建表后添加索引</span></span><br><span class="line"><span class="keyword">CREATE</span> INDEX 索引名 <span class="keyword">ON</span> 表名 (字段<span class="number">1</span>，字段<span class="number">2</span>);</span><br></pre></td></tr></table></figure></li>
</ul>
<h4 id="聚簇索引和非聚簇索引"><a href="#聚簇索引和非聚簇索引" class="headerlink" title="聚簇索引和非聚簇索引"></a>聚簇索引和非聚簇索引</h4><p>聚簇索引将数据和索引放在一起；非聚簇分开存储。</p>
<p>辅助索引是在聚簇索引之上建立的索引，<strong>非聚簇索引都是辅助索引</strong>。</p>
<p>辅助键索引叶子结点存放主键，不存放地址的原因是防止增删改导致地址变化。</p>
<p><img src="https://gitee.com/yuzhenxin/images/raw/master/img/20210313155746.png" alt="image-20210313155746264"></p>
<p>非聚簇索引<strong>指向地址</strong>，单次查询效率高于聚簇索引。但是聚簇索引一次查询会将一页存入内存，下次访问同一页的数据就不必再访问磁盘，且在增删改时便于维护。</p>
<p><img src="https://gitee.com/yuzhenxin/images/raw/master/img/20210313203941.png" alt="image-20210313203934463"></p>
<h4 id="使用场景"><a href="#使用场景" class="headerlink" title="使用场景"></a>使用场景</h4><ol>
<li>需要建立：<ul>
<li>主键</li>
<li>常做查询条件的字段</li>
<li>外键关联</li>
<li>查询排序字段</li>
<li>统计分组字段</li>
</ul>
</li>
<li>不要建立：<ul>
<li>表记录少</li>
<li>经常修改的字段</li>
<li>where用不上的字段</li>
<li>过滤性差的字段（性别等）</li>
</ul>
</li>
</ol>
<h3 id="一些题目"><a href="#一些题目" class="headerlink" title="一些题目"></a>一些题目</h3><p>一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录， 再把 Mysql 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15 ？</p>
<ul>
<li>如果表的类型是 MyISAM，那么是 18 因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里，重启 MySQL 自增主键的最大 ID 也不会丢失</li>
<li>如果表的类型是 InnoDB，那么是 15 InnoDB 表只是把自增主键的最大 ID 记录到内存中，所以重启数据库或者是对表进行 OPTIMIZE 操作，都会导致最大 ID 丢失</li>
</ul>
<hr>
<p>CHAR 列长度固定为创建表时声明的长度，长度值范围是 1 到 255。当 CHAR 值被存储时，它们被用<strong>空格</strong>填充到特定长度，检索 CHAR 值时需删除尾随空格</p>
<h2 id="Redis"><a href="#Redis" class="headerlink" title="Redis"></a>Redis</h2><h3 id="数据类型："><a href="#数据类型：" class="headerlink" title="数据类型："></a>数据类型：</h3><ol>
<li>String：key-value，最大512MB。SET\GET</li>
<li>Hash：key-value集合，适合存储对象。HMSET、HGET。存储用户信息，Hset(key,field,value)，如Hset(userKey,id,10)，通过Hget(userKey,id)获得</li>
<li>List：有序链表(底层是双向链表)，rpush、lpush、lrange key start stop，用于最新消息排行，模拟消息队列（如电商秒杀）</li>
<li>Set：String的无序集合。sadd k v、smembers k，用于自动排重，求好友列表交集</li>
<li>Zset有序集合：String的有序集合。zadd k score v，用于条件排序（商品综合、价格排序）</li>
<li>HyperLogLog基数</li>
</ol>
<h3 id="事务"><a href="#事务" class="headerlink" title="事务"></a>事务</h3><p>MULTI -&gt; 命令 -&gt; EXEC</p>
<p>单条命令原子性，事务非原子性。</p>
<ul>
<li><p>命令格式错误，直接回滚</p>
</li>
<li><p>命令格式正确，操作数据引发错误继续执行后续命令</p>
</li>
</ul>
<p>watch -&gt; 执行事务 -&gt; 回滚与否都unwatch</p>
<h3 id="持久化"><a href="#持久化" class="headerlink" title="持久化"></a>持久化</h3><ul>
<li>RDB：单独创建fork进程进行持久化，指定时间间隔将内存的数据集快照写入磁盘<br><img src="https://i.imgur.com/CFQj3Ov.png" alt="RDB"></li>
<li>AOF：以日志的形式记录操作，只许追加<br><img src="https://i.imgur.com/ku158eo.png" alt="AOF"></li>
</ul>
<h3 id="雪崩、缓存穿透、缓存击穿"><a href="#雪崩、缓存穿透、缓存击穿" class="headerlink" title="雪崩、缓存穿透、缓存击穿"></a>雪崩、缓存穿透、缓存击穿</h3><ul>
<li>雪崩：双十一<strong>大量ke</strong>y在Redis同时<strong>缓存时间到</strong>，同时过期，大量访问同时发送给数据库<ul>
<li>解决办法：设置不同key值的过期时间不同，如随机值，或者直接永不失效……</li>
</ul>
</li>
<li>缓存穿透（穿透攻击）：黑客故意发送一直不存在的id（如负数），Redis中<strong>没有</strong>，就不断向数据库发送请求<ul>
<li>解决办法：<strong>布隆过滤器</strong>、请求参数检查、限制IP访问等</li>
</ul>
</li>
<li>缓存击穿（击穿热点）：秒杀商品<strong>缓存时间到</strong>，对<strong>该key</strong>的大量访问发给数据库<ul>
<li>解决办法是在Redis和数据库之间用<strong>分布式锁</strong>，如单线程访问数据库，拿到数据后写回Redis，其他请求就可以重新从Redis拿数据了</li>
</ul>
</li>
</ul>
<h3 id="布隆过滤器"><a href="#布隆过滤器" class="headerlink" title="布隆过滤器"></a>布隆过滤器</h3><p>布隆过滤器用在前端和Redis之间，相当于白名单、黑名单，主要用于<strong>防止缓存穿透</strong>。布隆过滤器包含了数据库的数据，前端数据首先通过布隆过滤器，再发给Redis，有则返回数据；无则发给数据库，有则写回Redis并返回，无则返回空（布隆过滤器误判了）</p>
<h4 id="原理"><a href="#原理" class="headerlink" title="原理"></a>原理</h4><p>将一个值经过多个Hash函数得到不同的值存入数组对应位置中，a[Hash(“string”)] = 1。</p>
<p>优点：</p>
<ul>
<li>一个存放一个二进制位的数组，占用空间小</li>
<li>插入和查询速度快</li>
</ul>
<p>缺点</p>
<ul>
<li>不便于删除值。（有可能不同的值经过hash得到相同的值）</li>
<li>误判 </li>
</ul>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">RedissonBloomFilter</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">static</span> RedissonClient redisson = <span class="keyword">null</span>;</span><br><span class="line">    <span class="keyword">static</span> RBloomFilter&lt;String&gt; bloomFilter = <span class="keyword">null</span>;</span><br><span class="line"></span><br><span class="line">    <span class="keyword">static</span> &#123;</span><br><span class="line">        Config config = <span class="keyword">new</span> Config();</span><br><span class="line">        config.useSingleServer().setAddress(<span class="string">&quot;redis://192.168.245.130:6379&quot;</span>);</span><br><span class="line"></span><br><span class="line">        <span class="comment">//构造Redisson</span></span><br><span class="line">        redisson = Redisson.create(config);</span><br><span class="line"></span><br><span class="line">        bloomFilter = redisson.getBloomFilter(<span class="string">&quot;userIdFilter&quot;</span>);</span><br><span class="line">        initData(redisson, bloomFilter);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">private</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">initData</span><span class="params">(RedissonClient redisson, RBloomFilter&lt;String&gt; bloomFilter)</span> </span>&#123;</span><br><span class="line">        <span class="comment">//初始化布隆过滤器：预计元素为100000000L,误差率为1%</span></span><br><span class="line">        bloomFilter.tryInit(<span class="number">100000000L</span>, <span class="number">0.01</span>);</span><br><span class="line"></span><br><span class="line">        <span class="comment">//将id为1和2插入到布隆过滤器中（只有id为1和2的放行）</span></span><br><span class="line">        bloomFilter.add(<span class="string">&quot;1&quot;</span>);</span><br><span class="line">        bloomFilter.add(<span class="string">&quot;2&quot;</span>);</span><br><span class="line"></span><br><span class="line">        <span class="comment">//只将id为1对应的user数据插入redis，访问2则需访问数据库</span></span><br><span class="line">        redisson.getBucket(<span class="string">&quot;1&quot;</span>).set(<span class="string">&quot;&#123;id:1, userName:&#x27;张三&#x27;, age:18&#125;&quot;</span>);</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">void</span> <span class="title">main</span><span class="params">(String[] args)</span> </span>&#123;</span><br><span class="line">        <span class="comment">// 模拟缓存穿透</span></span><br><span class="line">        User user = getUserById(<span class="number">2L</span>);</span><br><span class="line">        System.out.println(<span class="string">&quot;user对象为：&quot;</span> + JSON.toJSONString(user));</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="function"><span class="keyword">public</span> <span class="keyword">static</span> User <span class="title">getUserById</span><span class="params">(Long id)</span> </span>&#123;</span><br><span class="line">        <span class="keyword">if</span> (<span class="keyword">null</span> == id)</span><br><span class="line">            <span class="keyword">return</span> <span class="keyword">null</span>;</span><br><span class="line">        String idKey = id.toString();</span><br><span class="line"></span><br><span class="line">        <span class="comment">// 开始模拟缓存穿透</span></span><br><span class="line">        <span class="comment">// 前端查询key</span></span><br><span class="line">        <span class="keyword">if</span> (bloomFilter.contains(idKey)) &#123;</span><br><span class="line">            <span class="comment">// 通过过滤器白名单，去Redis查询</span></span><br><span class="line">            RBucket&lt;Object&gt; bucket = redisson.getBucket(idKey);</span><br><span class="line">            Object object = bucket.get();</span><br><span class="line"></span><br><span class="line">            <span class="comment">// redis查到了，直接返回</span></span><br><span class="line">            <span class="keyword">if</span> (<span class="keyword">null</span> != object) &#123;</span><br><span class="line">                System.out.println(<span class="string">&quot;从redis拿到了数据&quot;</span>);</span><br><span class="line">                String userStr = object.toString();</span><br><span class="line">                <span class="keyword">return</span> JSON.parseObject(userStr, User.class);</span><br><span class="line">            &#125;</span><br><span class="line"></span><br><span class="line">            <span class="comment">// redis查不到，去查数据库</span></span><br><span class="line">            System.out.println(<span class="string">&quot;访问数据库了&quot;</span>);</span><br><span class="line">            User user = selectByDb(idKey);</span><br><span class="line">            <span class="keyword">if</span> (<span class="keyword">null</span> == user)</span><br><span class="line">                <span class="keyword">return</span> <span class="keyword">null</span>;</span><br><span class="line">            <span class="keyword">else</span></span><br><span class="line">                redisson.getBucket(idKey).set(user.toString());</span><br><span class="line">            <span class="keyword">return</span> user;</span><br><span class="line">        &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">            System.out.println(<span class="string">&quot;布隆过滤器拦截了&quot;</span>);</span><br><span class="line">            <span class="keyword">return</span> <span class="keyword">null</span>;</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">    <span class="comment">// 模拟连接数据库，此处要啥数据都有</span></span><br><span class="line">    <span class="function"><span class="keyword">private</span> <span class="keyword">static</span> User <span class="title">selectByDb</span><span class="params">(String idKey)</span> </span>&#123;</span><br><span class="line">        User user = <span class="keyword">new</span> User(Long.parseLong(idKey), <span class="string">&quot;张三&quot;</span>, <span class="number">18</span>);</span><br><span class="line">        <span class="keyword">return</span> user;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h3 id="redis在高并发下的使用案例"><a href="#redis在高并发下的使用案例" class="headerlink" title="redis在高并发下的使用案例"></a>redis在高并发下的使用案例</h3><p>如下为不断优化后的结果</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@RestController</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">RedisController</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Autowired</span></span><br><span class="line">    StringRedisTemplate stringRedisTemplate;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@GetMapping(&quot;/deduct&quot;)</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> String <span class="title">deductStock</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        String lockKey = <span class="string">&quot;product_101&quot;</span>;</span><br><span class="line">        <span class="comment">//模拟唯一的client</span></span><br><span class="line">        String clientId = UUID.randomUUID().toString();</span><br><span class="line"></span><br><span class="line">        <span class="comment">//防止过程中抛异常，而最终无论如何都要释放锁</span></span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            <span class="comment">// 加锁的同时设置加锁者和过期时间</span></span><br><span class="line">            Boolean result = stringRedisTemplate.opsForValue().setIfAbsent(lockKey, clientId, <span class="number">30</span>, TimeUnit.SECONDS);</span><br><span class="line">            <span class="keyword">if</span> (!result)</span><br><span class="line">                <span class="keyword">return</span> <span class="string">&quot;error_code&quot;</span>;</span><br><span class="line"></span><br><span class="line">            <span class="keyword">int</span> stock = Integer.parseInt(stringRedisTemplate.opsForValue().get(<span class="string">&quot;stock&quot;</span>));</span><br><span class="line">            <span class="keyword">if</span> (stock &gt; <span class="number">0</span>) &#123;</span><br><span class="line">                <span class="keyword">int</span> realStock = stock - <span class="number">1</span>;</span><br><span class="line">                stringRedisTemplate.opsForValue().set(<span class="string">&quot;stock&quot;</span>, realStock + <span class="string">&quot;&quot;</span>);</span><br><span class="line">                System.out.println(<span class="string">&quot;扣减成功，剩余库存：&quot;</span> + realStock);</span><br><span class="line">            &#125; <span class="keyword">else</span></span><br><span class="line">                System.out.println(<span class="string">&quot;扣减失败，库存不足&quot;</span>);</span><br><span class="line">        &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">            <span class="comment">// 确定是谁加的锁，才能释放锁</span></span><br><span class="line">            <span class="keyword">if</span> (clientId.equals(stringRedisTemplate.opsForValue().get(lockKey)))</span><br><span class="line">                stringRedisTemplate.delete(lockKey);</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="keyword">return</span> <span class="string">&quot;end&quot;</span>;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p>但是在失效时间和finally的if处仍有问题，于是使用Redisson解决</p>
<figure class="highlight java"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta">@RestController</span></span><br><span class="line"><span class="keyword">public</span> <span class="class"><span class="keyword">class</span> <span class="title">RedisController</span> </span>&#123;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Autowired</span></span><br><span class="line">    StringRedisTemplate stringRedisTemplate;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@Autowired</span></span><br><span class="line">    Redisson redisson;</span><br><span class="line"></span><br><span class="line">    <span class="meta">@GetMapping(&quot;/deduct&quot;)</span></span><br><span class="line">    <span class="function"><span class="keyword">public</span> String <span class="title">deductStock</span><span class="params">()</span> </span>&#123;</span><br><span class="line">        String lockKey = <span class="string">&quot;product_101&quot;</span>;</span><br><span class="line"></span><br><span class="line">        RLock redissonLock = redisson.getLock(lockKey);</span><br><span class="line"></span><br><span class="line">        <span class="keyword">try</span> &#123;</span><br><span class="line">            redissonLock.lock();</span><br><span class="line"></span><br><span class="line">            <span class="keyword">int</span> stock = Integer.parseInt(stringRedisTemplate.opsForValue().get(<span class="string">&quot;stock&quot;</span>));</span><br><span class="line">            <span class="keyword">if</span> (stock &gt; <span class="number">0</span>) &#123;</span><br><span class="line">                <span class="keyword">int</span> realStock = stock - <span class="number">1</span>;</span><br><span class="line">                stringRedisTemplate.opsForValue().set(<span class="string">&quot;stock&quot;</span>, realStock + <span class="string">&quot;&quot;</span>);</span><br><span class="line">                System.out.println(<span class="string">&quot;扣减成功，剩余库存：&quot;</span> + realStock);</span><br><span class="line">            &#125; <span class="keyword">else</span></span><br><span class="line">                System.out.println(<span class="string">&quot;扣减失败，库存不足&quot;</span>);</span><br><span class="line">        &#125; <span class="keyword">finally</span> &#123;</span><br><span class="line">            redissonLock.unlock();</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        <span class="keyword">return</span> <span class="string">&quot;end&quot;</span>;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<p>redisson底层使用的lua脚本在redis当成一条原子性指令执行，上述代码问题在于主从节点的同步问题</p>
<h3 id="实现分布式锁zookeeper和redis对比"><a href="#实现分布式锁zookeeper和redis对比" class="headerlink" title="实现分布式锁zookeeper和redis对比"></a>实现分布式锁zookeeper和redis对比</h3><ul>
<li>在CAP即一致性（Consistency）、可用性（Availability）、分区容错性（Partition tolerance）上：<ul>
<li>zookeeper侧重CP，加锁后先同步给从节点至主+从占半数以上，再返回；如果主节点宕机后选取已同步的从节点作主节点</li>
<li>redis侧重AP，线程访问redis，加锁后立刻返回，性能较高</li>
</ul>
</li>
</ul>
<p>分布式锁还可以用于数据库和缓存双写一致性问题</p>

    </div>

    
    
    

      <footer class="post-footer">

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/2020/10/15/Java%E5%9F%BA%E7%A1%80/" rel="prev" title="Java基础">
      <i class="fa fa-chevron-left"></i> Java基础
    </a></div>
      <div class="post-nav-item">
    <a href="/2020/12/23/%E8%AE%A1%E7%AE%97%E6%9C%BA%E5%9F%BA%E7%A1%80/" rel="next" title="计算机基础">
      计算机基础 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="nav-number">1.</span> <span class="nav-text">数据库</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB"><span class="nav-number">1.1.</span> <span class="nav-text">隔离级别</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#MySQL"><span class="nav-number">1.2.</span> <span class="nav-text">MySQL</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B7%A5%E4%BD%9C%E5%B8%B8%E7%94%A8%E5%88%B0%E7%9A%84%E8%AF%AD%E5%8F%A5"><span class="nav-number">1.2.1.</span> <span class="nav-text">工作常用到的语句</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#Dataworks%E8%AF%AD%E6%B3%95"><span class="nav-number">1.2.2.</span> <span class="nav-text">Dataworks语法</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E8%A1%A8%E7%9A%84%E6%93%8D%E4%BD%9C"><span class="nav-number">1.2.2.1.</span> <span class="nav-text">表的操作</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#join"><span class="nav-number">1.2.3.</span> <span class="nav-text">join</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#on%E5%92%8Cwhere"><span class="nav-number">1.2.3.1.</span> <span class="nav-text">on和where</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%93%8D%E4%BD%9C"><span class="nav-number">1.2.3.2.</span> <span class="nav-text">数据库的操作</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#JDBC%E5%AF%86%E7%A0%81%E9%AA%8C%E8%AF%81"><span class="nav-number">1.2.4.</span> <span class="nav-text">JDBC密码验证</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%A4%87%E4%BB%BD%E4%B8%8E%E6%81%A2%E5%A4%8D"><span class="nav-number">1.2.5.</span> <span class="nav-text">备份与恢复</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E7%B4%A2%E5%BC%95"><span class="nav-number">1.2.6.</span> <span class="nav-text">索引</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E5%88%86%E7%B1%BB"><span class="nav-number">1.2.6.1.</span> <span class="nav-text">分类</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95%E5%92%8C%E9%9D%9E%E8%81%9A%E7%B0%87%E7%B4%A2%E5%BC%95"><span class="nav-number">1.2.6.2.</span> <span class="nav-text">聚簇索引和非聚簇索引</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#%E4%BD%BF%E7%94%A8%E5%9C%BA%E6%99%AF"><span class="nav-number">1.2.6.3.</span> <span class="nav-text">使用场景</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%80%E4%BA%9B%E9%A2%98%E7%9B%AE"><span class="nav-number">1.2.7.</span> <span class="nav-text">一些题目</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#Redis"><span class="nav-number">1.3.</span> <span class="nav-text">Redis</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B%EF%BC%9A"><span class="nav-number">1.3.1.</span> <span class="nav-text">数据类型：</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BA%8B%E5%8A%A1"><span class="nav-number">1.3.2.</span> <span class="nav-text">事务</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%8C%81%E4%B9%85%E5%8C%96"><span class="nav-number">1.3.3.</span> <span class="nav-text">持久化</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E9%9B%AA%E5%B4%A9%E3%80%81%E7%BC%93%E5%AD%98%E7%A9%BF%E9%80%8F%E3%80%81%E7%BC%93%E5%AD%98%E5%87%BB%E7%A9%BF"><span class="nav-number">1.3.4.</span> <span class="nav-text">雪崩、缓存穿透、缓存击穿</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%B8%83%E9%9A%86%E8%BF%87%E6%BB%A4%E5%99%A8"><span class="nav-number">1.3.5.</span> <span class="nav-text">布隆过滤器</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#%E5%8E%9F%E7%90%86"><span class="nav-number">1.3.5.1.</span> <span class="nav-text">原理</span></a></li></ol></li><li class="nav-item nav-level-3"><a class="nav-link" href="#redis%E5%9C%A8%E9%AB%98%E5%B9%B6%E5%8F%91%E4%B8%8B%E7%9A%84%E4%BD%BF%E7%94%A8%E6%A1%88%E4%BE%8B"><span class="nav-number">1.3.6.</span> <span class="nav-text">redis在高并发下的使用案例</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%AE%9E%E7%8E%B0%E5%88%86%E5%B8%83%E5%BC%8F%E9%94%81zookeeper%E5%92%8Credis%E5%AF%B9%E6%AF%94"><span class="nav-number">1.3.7.</span> <span class="nav-text">实现分布式锁zookeeper和redis对比</span></a></li></ol></li></ol></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">余振鑫</p>
  <div class="site-description" itemprop="description">余振鑫的个人博客</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/archives/">
        
          <span class="site-state-item-count">9</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
  </nav>
</div>



      </div>

    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 
  <span itemprop="copyrightYear">2022</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">余振鑫</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://muse.theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Muse</a> 强力驱动
  </div>

        








      </div>
    </footer>
  </div>

  
  <script src="/lib/anime.min.js"></script>
  <script src="/lib/velocity/velocity.min.js"></script>
  <script src="/lib/velocity/velocity.ui.min.js"></script>

<script src="/js/utils.js"></script>

<script src="/js/motion.js"></script>


<script src="/js/schemes/muse.js"></script>


<script src="/js/next-boot.js"></script>




  















  

  

</body>
</html>
